import xlwt
import xlrd
import math

data=[]
data1=[]
wb=xlrd.open_workbook(r'F:\02 工程设计项目\2024工作项目\03 南昌局\02  沪昆高铁无砟轨道预加固施工图设计-第二批（安全生产费）'
                      r'\02 设计文件\01 沪昆江西\06 沪昆高速线上下行K493+000-K508+300内24km连续型无砟轨道进行预防性加固'
                      r'\2024年沪昆高速上行K493+000-K508+300，下行K493+000-K508+300无砟轨道植筋读取.xlsx')
sheet=wb.sheets()[0]
for a in range(0,sheet.nrows):
    cells=sheet.row_values(a)
    ZJ_Z=cells[4]#植筋总数
    ZJ_1=cells[5]   #1号枕植筋数量
    ZJ_2=cells[6]#2号枕植筋数量
    ZJ_3=cells[7]   #3号枕植筋数量
    ZJ_4=cells[8]#4号枕植筋数量
    ZJ_5=cells[9]   #5号枕植筋数量
    ZJ_6=cells[10]#6号枕植筋数量
    ZJ_7=cells[11]   #7号枕植筋数量
    ZJ_8=cells[12]#8号枕植筋数量
    ZJ_9=cells[13]   #9号枕植筋数量
    ZJ_10=cells[14]#10号枕植筋数量
    ZJ_X=cells[15]   #需要植筋数量
    ZJ_SZ = None
    ZJ_FA = None
    if ZJ_X == 4 :
        if ZJ_Z <= 8 :
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案一'
        else:
            ZJ_SZ_1 = ZJ_X - ZJ_1 - ZJ_9 - ZJ_10
            ZJ_SZ_2 = ZJ_X - ZJ_1 - ZJ_2 - ZJ_10
            ZJ_SZ =max(ZJ_SZ_1,ZJ_SZ_2)
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案一'
    elif ZJ_X == 8:
        if ZJ_Z <= 12:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案二'
    elif ZJ_X == 10:
        if ZJ_Z <= 8:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 10:
                ZJ_FA = '方案三'
        else:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_3 - ZJ_8 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 10:
                ZJ_FA = '方案三'
    elif ZJ_X == 12:
        if ZJ_Z <= 16:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_3 - ZJ_8 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 12:
                ZJ_FA = '方案四'
    elif ZJ_X == 16:
            ZJ_SZ = ZJ_X - ZJ_Z
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 16:
                ZJ_FA = '方案五'
    data.append([cells[0],cells[1],cells[2],cells[3],cells[4],cells[5],cells[6],cells[7],cells[8],cells[9],cells[10],cells[11],cells[12],cells[13],cells[14],cells[15],ZJ_SZ,ZJ_FA])

sc=xlwt.Workbook("jieguo")
sheet_jieguo=sc.add_sheet('上行',cell_overwrite_ok=True)
i=0
for m in data:
    sheet_jieguo.write(i,0,m[0])
    sheet_jieguo.write(i,1,m[1])
    sheet_jieguo.write(i,2,m[2])
    sheet_jieguo.write(i,3,m[3])
    sheet_jieguo.write(i,4,m[4])
    sheet_jieguo.write(i,5,m[5])
    sheet_jieguo.write(i,6,m[6])
    sheet_jieguo.write(i,7,m[7])
    sheet_jieguo.write(i,8,m[8])
    sheet_jieguo.write(i,9,m[9])
    sheet_jieguo.write(i,10,m[10])
    sheet_jieguo.write(i,11,m[11])
    sheet_jieguo.write(i,12,m[12])
    sheet_jieguo.write(i, 13, m[13])
    sheet_jieguo.write(i, 14, m[14])
    sheet_jieguo.write(i, 15, m[15])
    sheet_jieguo.write(i, 16, m[16])
    sheet_jieguo.write(i, 17, m[17])
    i = i + 1

sheet1=wb.sheets()[1]
for a in range(0,sheet1.nrows):
    cells=sheet1.row_values(a)
    ZJ_Z=cells[4]#植筋总数
    ZJ_1=cells[5]   #1号枕植筋数量
    ZJ_2=cells[6]#2号枕植筋数量
    ZJ_3=cells[7]   #3号枕植筋数量
    ZJ_4=cells[8]#4号枕植筋数量
    ZJ_5=cells[9]   #5号枕植筋数量
    ZJ_6=cells[10]#6号枕植筋数量
    ZJ_7=cells[11]   #7号枕植筋数量
    ZJ_8=cells[12]#8号枕植筋数量
    ZJ_9=cells[13]   #9号枕植筋数量
    ZJ_10=cells[14]#10号枕植筋数量
    ZJ_X=cells[15]   #需要植筋数量

    if ZJ_X == 4 :
        if ZJ_Z <= 8 :
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案一'
        else:
            ZJ_SZ_1 = ZJ_X - ZJ_1 - ZJ_9 - ZJ_10
            ZJ_SZ_2 = ZJ_X - ZJ_1 - ZJ_2 - ZJ_10
            ZJ_SZ =max(ZJ_SZ_1,ZJ_SZ_2)
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案一'
    elif ZJ_X == 8:
        if ZJ_Z <= 12:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案二'
    elif ZJ_X == 10:
        if ZJ_Z <= 8:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 10:
                ZJ_FA = '方案三'
        else:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_3 - ZJ_8 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 10:
                ZJ_FA = '方案三'
    elif ZJ_X == 12:
        if ZJ_Z <= 16:
            ZJ_SZ = ZJ_X - ZJ_1 - ZJ_2 - ZJ_3 - ZJ_8 - ZJ_9 - ZJ_10
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 12:
                ZJ_FA = '方案四'
    elif ZJ_X == 16:
            ZJ_SZ = ZJ_X - ZJ_Z
            if ZJ_SZ <= 0:
                ZJ_SZ = 0
                ZJ_FA = '方案六'
            elif ZJ_SZ == 2:
                ZJ_FA = '方案七'
            elif ZJ_SZ == 3:
                ZJ_FA = '方案八'
            elif ZJ_SZ == 4:
                ZJ_FA = '方案九'
            elif ZJ_SZ == 6:
                ZJ_FA = '方案十一'
            elif ZJ_SZ == 8:
                ZJ_FA = '方案十二'
            elif ZJ_SZ == 16:
                ZJ_FA = '方案五'
    data1.append([cells[0],cells[1],cells[2],cells[3],cells[4],cells[5],cells[6],cells[7],cells[8],cells[9],cells[10],cells[11],cells[12],cells[13],cells[14],cells[15],ZJ_SZ,ZJ_FA])

sheet_jieguo=sc.add_sheet('下行',cell_overwrite_ok=True)
i=0
for m in data1:
    sheet_jieguo.write(i,0,m[0])
    sheet_jieguo.write(i,1,m[1])
    sheet_jieguo.write(i,2,m[2])
    sheet_jieguo.write(i,3,m[3])
    sheet_jieguo.write(i,4,m[4])
    sheet_jieguo.write(i,5,m[5])
    sheet_jieguo.write(i,6,m[6])
    sheet_jieguo.write(i,7,m[7])
    sheet_jieguo.write(i,8,m[8])
    sheet_jieguo.write(i,9,m[9])
    sheet_jieguo.write(i,10,m[10])
    sheet_jieguo.write(i,11,m[11])
    sheet_jieguo.write(i,12,m[12])
    sheet_jieguo.write(i, 13, m[13])
    sheet_jieguo.write(i, 14, m[14])
    sheet_jieguo.write(i, 15, m[15])
    sheet_jieguo.write(i, 16, m[16])
    sheet_jieguo.write(i, 17, m[17])
    i = i + 1

sc.save('zhijinfangan.xls')

